package xyz.scootaloo.draft.jdbc;

import java.sql.*;

/**
 * @author flutterdash@qq.com
 * @since 2021/5/3 8:49
 */
public class HelloJDBC {

    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/sql_test?" +
            "useUnicode=true&characterEncoding=utf-8&" +
            "useSSL=false&serverTimezone=GMT%2B8&" +
            "allowPublicKeyRetrieval=true";

    //  Database credentials
    static final String USER = "root";
    static final String PASS = "admin";

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);

            //STEP 3: Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            //STEP 4: Execute a query
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM student";
            ResultSet rs = stmt.executeQuery(sql);

            //STEP 5: Extract data from result set
            while (rs.next()) {
                //Retrieve by column name
                Student student = new Student();
                student.id = rs.getString(1);
                student.name = rs.getString(2);
                student.birthday = rs.getDate(3);
                student.sex = rs.getString(4);

                //Display values
                System.out.println(student);
            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception se) {
            //Handle errors for JDBC
            se.printStackTrace();
        }//Handle errors for Class.forName
        finally {
            //finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException ignored) {
            }
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }//end finally try
        }//end try
        System.out.println("There are so thing wrong!");
    }//end


    static class Student {
        String id;
        String name;
        Date birthday;
        String sex;

        @Override
        public String toString() {
            return "Student{" +
                    "id='" + id + '\'' +
                    ", name='" + name + '\'' +
                    ", birthday=" + birthday +
                    ", sex='" + sex + '\'' +
                    '}';
        }
    }

}
